package org.hgvc.webapp.ets.spring.dao;

import java.util.Date;
import java.util.List;

import org.hgvc.webapp.ets.bo.AdvancePayment;
import org.hgvc.webapp.ets.bo.AgentCommission;
import org.hgvc.webapp.ets.bo.Agreement;
import org.hgvc.webapp.ets.bo.Finance;
import org.hgvc.webapp.ets.bo.InvoiceDtl;
import org.hgvc.webapp.ets.bo.Payment;
import org.hgvc.webapp.ets.bo.PaymentDtl;
import org.hgvc.webapp.ets.bo.StandingInstruction;
import org.hgvc.webapp.ets.constant.CommonConstant;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.jocsb.base.AbstractHibernateDao;


public class FinanceHbmDao extends AbstractHibernateDao{

	
	public Double getTotalPaidInstallment(String agreementId) throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select sum( pdtl.amount ) from PaymentDtl pdtl , InvoiceDtl inv where pdtl.payment.finance.agreement.agreementId=:agreementId and " +
				"		pdtl.payment.paymentId = inv.invoice.payment.paymentId " +
				"   " +
				"	and ( inv.itemType=:typeInstallment1 "+
				"	or inv.itemType=:typeInstallment2  " +
				" or inv.itemType=:typeInstallment3 or inv.itemType=:typeInstallment4)");
		
		Query query = getSession().createQuery(str.toString());
		
		query.setParameter("agreementId", agreementId); 
		query.setParameter("typeInstallment1", CommonConstant.INVOICE_TYPE_INSTALLMENT);
		query.setParameter("typeInstallment2", CommonConstant.INVOICE_TYPE_DEPOSIT_INSTALLMENT);
		query.setParameter("typeInstallment3", CommonConstant.INVOICE_TYPE_DEPOSIT_MEMBERSHIP);
		query.setParameter("typeInstallment4", CommonConstant.INVOICE_TYPE_DEPOSIT);	
		List list = query.list();
		if (list==null || list.size()==0 ||list.get(0)==null){
			return 0d;
		}
		double ret = Double.parseDouble( list.get(0)+"");
		return ret;
	}

	public Double getTotalPaidInstallment(String agreementId, int month, int year) throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select sum( pdtl.amount ) from PaymentDtl pdtl , InvoiceDtl inv where pdtl.payment.finance.agreement.agreementId=:agreementId and " +
				"	pdtl.payment.paymentId = inv.invoice.payment.paymentId " +
				" " +
				"	and ( inv.itemType=:typeInstallment1 "+
				"	or inv.itemType=:typeInstallment2  " +
				" or inv.itemType=:typeInstallment3 or inv.itemType=:typeInstallment4) " +
				" and month(pdtl.payment.paymentDate)=:month"+
				" and year(pdtl.payment.paymentDate)=:year");
		
		Query query = getSession().createQuery(str.toString());
		
		query.setParameter("agreementId", agreementId);
		query.setParameter("year", year);
		query.setParameter("month", month); 
		query.setParameter("typeInstallment1", CommonConstant.INVOICE_TYPE_INSTALLMENT);
		query.setParameter("typeInstallment2", CommonConstant.INVOICE_TYPE_DEPOSIT_INSTALLMENT);
		query.setParameter("typeInstallment3", CommonConstant.INVOICE_TYPE_DEPOSIT_MEMBERSHIP);
		query.setParameter("typeInstallment4", CommonConstant.INVOICE_TYPE_DEPOSIT);		
		List list = query.list();
		if (list==null || list.size()==0 ||list.get(0)==null){
			return 0d;
		}
		double ret = Double.parseDouble( list.get(0)+"");
		return ret;
	}
	public Finance getFinanceByProspectId(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select fin from  Finance fin where fin.prospectInfo.prospectInfoId=:id and fin.isDeleted=:value ");
		
		Query query = getSession().createQuery(str.toString());
		
		query.setParameter("id", id);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		
		return (Finance)query.uniqueResult();
	}
	public Agreement getAgreementByProspectId(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select a from Agreement a where a.prospectInfo.prospectInfoId=:id");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("id", id);
		
		return (Agreement)query.uniqueResult();
	}
	
	public List<InvoiceDtl> getInvoiceDtlList(String id)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select invoiceDtl ");
		str.append(" from InvoiceDtl invoiceDtl");
		str.append(" where invoiceDtl.invoice.refId =:id ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("id", id);
		
		return (List<InvoiceDtl>)query.list();
	}
	
	public List<PaymentDtl> getPaymentDtlList(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select paymentDtl ");
		str.append(" from PaymentDtl paymentDtl");
		str.append(" where paymentDtl.invoiceDtl.invoiceDtlId=:id");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("id", id);
		
		return (List<PaymentDtl>)query.list();
	}
	
	public List<InvoiceDtl> getInvoiceDtlListByMemberId(String memberId)throws Exception{
		Criteria invDtlCrt = getSession().createCriteria(InvoiceDtl.class);
		Criteria invCrt = invDtlCrt.createCriteria("invoice");
		Criteria financeCrt = invCrt.createCriteria("finance");
		Criteria memberCrt= financeCrt.createCriteria("member");
		memberCrt.add(Restrictions.eq("memberId", memberId));
		invDtlCrt.add(Restrictions.eq("status", CommonConstant.INVOICE_STATUS_ACTIVE));
		invDtlCrt.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		List<InvoiceDtl> list=invDtlCrt.list();
		return list;
	}
	
	public AdvancePayment getAdvancePaymentByMemberId(String memberId)throws Exception{
		Criteria advPaymentCrt = getSession().createCriteria(AdvancePayment.class);
		Criteria financeCrt = advPaymentCrt.createCriteria("finance");
		Criteria memberCrt = financeCrt.createCriteria("member");
		memberCrt.add(Restrictions.eq("memberId", memberId));
		advPaymentCrt.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		
		AdvancePayment advancePayment=null;
		List<AdvancePayment> list=advPaymentCrt.list();
		if (list.size()>0)
			advancePayment = list.get(0);
		return advancePayment;
	}
	
	public Double getTotalInvoiceAmountByMemberId(String memberId)throws Exception{
		Criteria invDtlCrt = getSession().createCriteria(InvoiceDtl.class);
		Criteria invCrt = invDtlCrt.createCriteria("invoice");
		Criteria financeCrt = invCrt.createCriteria("finance");
		Criteria memberCrt= financeCrt.createCriteria("member");
		memberCrt.add(Restrictions.eq("memberId", memberId));
		invDtlCrt.add(Restrictions.eq("status", CommonConstant.INVOICE_STATUS_ACTIVE));
		invDtlCrt.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		invDtlCrt.setProjection(Projections.sum("itemAmount"));
		
		Double dbInvAmt =null;
		List<Double> list = invDtlCrt.list();
		if (list.size()>0)
			dbInvAmt = list.get(0);
		return dbInvAmt;
	}
	
	public Double getTotalInvoicePaidAmountByMemberId(String memberId)throws Exception{
		Criteria invDtlCrt = getSession().createCriteria(InvoiceDtl.class);
		Criteria invCrt = invDtlCrt.createCriteria("invoice");
		Criteria financeCrt = invCrt.createCriteria("finance");
		Criteria memberCrt= financeCrt.createCriteria("member");
		memberCrt.add(Restrictions.eq("memberId", memberId));
		invDtlCrt.add(Restrictions.eq("status", CommonConstant.INVOICE_STATUS_ACTIVE));
		invDtlCrt.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		invDtlCrt.setProjection(Projections.sum("paidAmount"));
		
		Double dbInvPaidAmt =null;
		List<Double> list = invDtlCrt.list();
		if (list.size()>0)
			dbInvPaidAmt = list.get(0);
		return dbInvPaidAmt;
	}
	
	public Finance getFinanceByMemberId(String memberId)throws Exception{
		Criteria financeCrt = getSession().createCriteria(Finance.class);
		Criteria memberCrt = financeCrt.createCriteria("member");
		memberCrt.add(Restrictions.eq("memberId", memberId));
		financeCrt.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		
		Finance finance=null;
		List<Finance> list=financeCrt.list();
		if (list.size()>0)
			finance=list.get(0);
		return finance;
	}
	
	public Finance getFinanceByAgreementId(String agreementId)throws Exception{
		Criteria financeCrt = getSession().createCriteria(Finance.class);
		Criteria agreementCrt = financeCrt.createCriteria("agreement");
		agreementCrt.add(Restrictions.eq("agreementId", agreementId));
		financeCrt.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		
		Finance finance=null;
		List<Finance> list=financeCrt.list();
		if (list.size()>0)
			finance=list.get(0);
		return finance;
	}
	
	public PaymentDtl getPaymentDtlByPaymentId(String paymentId)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select dtl ");
		str.append(" from PaymentDtl dtl");
		str.append(" where dtl.payment.paymentId =:paymentId ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("paymentId", paymentId);
		
		return (PaymentDtl)query.uniqueResult();
		
	}
	
	public List<InvoiceDtl> getInvoiceDtlByPaymentId(String paymentId)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select dtl ");
		str.append(" from InvoiceDtl dtl");
		str.append(" where dtl.invoice.payment.paymentId =:paymentId ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("paymentId", paymentId);
		
		return (List<InvoiceDtl>)query.list();
		
	}
	
	public List<Payment> getPaymentByMemberId(String memberId,String status)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select payment from Payment payment where payment.refId=:memberId and payment.status=:status ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("memberId", memberId);
		query.setParameter("status", status);
		return (List<Payment>)query.list();
		
	}
	
	public StandingInstruction getStandingInstructionByPaymentId(String paymentId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select ins from StandingInstruction ins where ins.payment.paymentId=:paymentId ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("paymentId", paymentId);
		return (StandingInstruction)query.uniqueResult();
	}
	
	public List<AdvancePayment> getAdvancePaymentListByAgreementId(String agreementId)throws Exception{
		Criteria advPaymentCrt = getSession().createCriteria(AdvancePayment.class);
		Criteria financeCrt = advPaymentCrt.createCriteria("finance");
		Criteria agreementCrt = financeCrt.createCriteria("agreement");
		agreementCrt.add(Restrictions.eq("agreementId", agreementId));
		advPaymentCrt.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		return advPaymentCrt.list();
	}
	
	public AdvancePayment getAdvancePaymentByFinanceId(String financeId)throws Exception{
		Criteria advPaymentCrt = getSession().createCriteria(AdvancePayment.class);
		Criteria financeCrt = advPaymentCrt.createCriteria("finance");
		financeCrt.add(Restrictions.eq("financeId", financeId));
		advPaymentCrt.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		return (AdvancePayment) advPaymentCrt.uniqueResult();
	}
	
	public List<Finance> getOverdueFinanceList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select distinct invDtl.invoice.finance from InvoiceDtl invDtl ");
		str.append("where invDtl.itemAmount>invDtl.paidAmount and invDtl.status=:status ");
		str.append("and invDtl.invoice.invoiceDueDate is not null and invDtl.invoice.invoiceDueDate<=:dueDate ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("status", CommonConstant.INVOICE_STATUS_ACTIVE );
		query.setParameter("dueDate", new Date() );
		
		List<Finance> list = query.list();
		return list;
	}
}
